
package com.huaweicloud.haydn.delivercore.agent.service.metadata.impl;

import static com.huaweicloud.haydn.delivercore.agent.common.exception.BusinessErrorEnum.BASE_INTERNAL_SERVER_ERROR;

import com.huaweicloud.haydn.delivercore.agent.common.exception.BusinessErrorEnum;
import com.huaweicloud.haydn.delivercore.agent.common.exception.BusinessException;
import com.huaweicloud.haydn.delivercore.agent.dto.excel.DbExcelDto;
import com.huaweicloud.haydn.delivercore.agent.dto.excel.FieldEmptyExcelDto;
import com.huaweicloud.haydn.delivercore.agent.dto.excel.FieldExcelDto;
import com.huaweicloud.haydn.delivercore.agent.dto.excel.TableExcelDto;
import com.huaweicloud.haydn.delivercore.agent.service.CommonUtils;

import lombok.extern.slf4j.Slf4j;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 功能描述
 *
 * @since 2023-03-10
 */
@Slf4j
@Service("sqlserver")
public class SqlserverDatabaseUtil extends BaseDatabaseUtil {

    @Autowired
    private CommonUtils commonUtils;

    @Override
    public String getDriverClass() {
        return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    }

    @Override
    public String getSelectedDatabasesSql(String dbNames) throws BusinessException {
        return "SELECT 8*SUM(b.dpages)/1024 table_phy_capacity, count(a.id) table_num FROM sysobjects a, sysindexes b where a.id = b.id "
            + "and a.xtype='U' AND b.indid=1";
    }

    @Override
    public String getAllTablesSql() {
        return "select c.name schema_name, a.name, 8*b.dpages/1024 table_phy_capacity, b.rows, 8*b.dpages*1024 "
            + "table_logical_capacity, CONVERT(varchar(200), d.value) from sysobjects a "
            + "left join sys.extended_properties d on d.major_id = a.id LEFT JOIN sysindexes b ON a.id = b.id AND b.indid = 1,"
            + "sys.schemas c where a.uid = c.schema_id  and a.xtype IN ( 'U', 'V' ) ";
    }

    @Override
    public String getAllTablesByDbSql(String dbNames) {
        return getAllTablesSql() + "and c.name IN " + dbNames;
    }

    @Override
    public String getSelectedTableSql(String dbNames, String tableNames) {
        return getAllTablesSql() + " and c.name IN " + dbNames + " and a.name IN " + tableNames;
    }

    @Override
    public String getAllFieldsSql() {
        return getFiledsCommonSql() + " order by a.id,a.colorder";
    }

    @Override
    public String getAllFieldsByDbSql(String dbNames) {
        return getFiledsCommonSql() + " and schemas.name in " + dbNames + " order by a.id,a.colorder";
    }

    @Override
    public String getAllFieldsSql(String dbNames, String tableNames) {
        return getFiledsCommonSql() + " and schemas.name in " + dbNames + " and d.name in " + tableNames
            + " order by a.id,a.colorder";
    }

    private String getFiledsCommonSql() {
        return "SELECT schema_name = schemas.name, table_name  = d.name, col_name_en = a.name, "
            + "field_type = b.name, field_len = COLUMNPROPERTY(a.id,a.name,'PRECISION'), "
            + "field_note = isnull(CONVERT(varchar(200), g.[value]),''), "
            + "is_primiry_key = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ("
            + "SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 'YES' else 'NO' end, "
            + "is_nullable = case when a.isnullable=1 then 'YES'else 'NO' end FROM syscolumns a "
            + "left join systypes b on a.xusertype=b.xusertype inner join sysobjects d "
            + "on a.id=d.id  and d.xtype IN ( 'U', 'V' ) and  d.name<>'dtproperties' left join syscomments e "
            + "on a.cdefault=e.id left join sys.extended_properties g "
            + "on a.id=G.major_id and a.colid=g.minor_id  left join sys.extended_properties f "
            + "on d.id=f.major_id and f.minor_id=0, sys.schemas schemas where d.uid = schemas.schema_id ";
    }

    @Override
    public String getURL(String hostname, String port, String databaseName) {
        return "jdbc:sqlserver://" + hostname + ":" + port + ";DatabaseName=" + databaseName;
    }

    @Override
    public List<DbExcelDto> getAllDbs(String datalinkName, String dbNames, String schemas) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = null;
        List<DbExcelDto> result = new ArrayList<>();
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            conn = getConnection();
            String sql = getSelectedDatabasesSql("");
            ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = ps.executeQuery();
            String today = sdf.format(new Date());
            while (rs.next()) {
                // 获取数据表中满足要求的一行数据，并放入Map中
                DbExcelDto dbExcelDto = new DbExcelDto();
                dbExcelDto.setDatabaseName(dbNames);
                dbExcelDto.setDatabaseTableNum(rs.getString(2));
                dbExcelDto.setDatabaseCapacity(rs.getString(1));
                dbExcelDto.setDatalinkName(datalinkName);
                dbExcelDto.setGenTime(today);
                result.add(dbExcelDto);
            }
            return result;
        } catch (SQLException e) {
            log.error(e.getMessage());
            throw new BusinessException(BusinessErrorEnum.BASE_INTERNAL_SERVER_ERROR);
        } finally {
            close(rs, ps, conn);
        }
    }

    @Override
    public List<TableExcelDto> getAllTables(String datalinkName, String dbNames, String schemas, String tableNames) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            String today = sdf.format(new Date());
            String sql;
            String realSchemas = commonUtils.sqlQueryBuild(schemas);
            String realTableNames = commonUtils.sqlQueryBuild(tableNames);
            if (StringUtils.isNotBlank(realSchemas) && StringUtils.isNotBlank(realTableNames)) {
                sql = getSelectedTableSql(realSchemas, realTableNames);
            } else if (StringUtils.isBlank(realSchemas) && StringUtils.isBlank(realTableNames)) {
                sql = getAllTablesSql();
            } else if (StringUtils.isNotBlank(realSchemas) && StringUtils.isBlank(realTableNames)) {
                sql = getAllTablesByDbSql(realSchemas);
            } else {
                throw new BusinessException(BASE_INTERNAL_SERVER_ERROR,
                    "if you configured table.name, you must configure db.name as well");
            }
            conn = getConnection();
            ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = ps.executeQuery();
            List<TableExcelDto> result = new ArrayList<>();
            while (rs.next()) {
                // 获取数据表中满足要求的一行数据，并放入Map中
                TableExcelDto tableExcelDto = new TableExcelDto();
                tableExcelDto.setGenTime(today);
                tableExcelDto.setDatalinkName(datalinkName);
                tableExcelDto.setDatabaseName(dbNames);
                tableExcelDto.setSchemaName(rs.getString(1));
                tableExcelDto.setTableName(rs.getString(2));
                tableExcelDto.setTablePhysicsCapacity(rs.getString(3));
                String tableRows = Optional.ofNullable(rs.getString(4)).orElse("0");
                tableExcelDto.setTableRows(tableRows);
                tableExcelDto.setTableDescription(rs.getString(6));
                if ("0".equals(tableRows)) {
                    tableExcelDto.setTableAvgLength("0");
                    tableExcelDto.setTableCapacity("0");
                } else {
                    int tableRowsInt = Integer.parseInt(tableRows);
                    String tableLogicaiCapacity = Optional.ofNullable(rs.getString(5)).orElse("0");
                    int logicalCapacity = Integer.parseInt(tableLogicaiCapacity);
                    tableExcelDto.setTableCapacity(tableLogicaiCapacity);
                    tableExcelDto.setTableAvgLength(logicalCapacity / tableRowsInt + "");
                }
                result.add(tableExcelDto);
            }
            return result;
        } catch (SQLException e) {
            log.error(e.getMessage());
            throw new BusinessException(BusinessErrorEnum.BASE_INTERNAL_SERVER_ERROR);
        } finally {
            close(rs, ps, conn);
        }
    }

    @Override
    public List<FieldExcelDto> getAllFields(String datalinkName, String dbNames, String schemas, String tableNames) {
        ResultSet rs = null;
        PreparedStatement ps = null;
        Connection conn = null;
        List<FieldExcelDto> result = new ArrayList<>();
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            String today = sdf.format(new Date());
            String sql;
            String realSchemas = commonUtils.sqlQueryBuild(schemas);
            String realTableNames = commonUtils.sqlQueryBuild(tableNames);
            sql = StringUtils.isBlank(realTableNames) ? getAllFieldsSql() : getAllFieldsSql("", realTableNames);
            if (StringUtils.isNotBlank(realSchemas) && StringUtils.isNotBlank(realTableNames)) {
                sql = getAllFieldsSql(realSchemas, realTableNames);
            } else if (StringUtils.isBlank(realSchemas) && StringUtils.isBlank(realTableNames)) {
                sql = getAllFieldsSql();
            } else if (StringUtils.isNotBlank(realSchemas) && StringUtils.isBlank(realTableNames)) {
                sql = getAllFieldsByDbSql(realSchemas);
            } else {
                throw new BusinessException(BASE_INTERNAL_SERVER_ERROR,
                    "if you configured table.name, you must configure db.name as well");
            }
            conn = getConnection();
            ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = ps.executeQuery();
            while (rs.next()) {
                FieldExcelDto fieldExcelDto = new FieldExcelDto();
                fieldExcelDto.setSchemaName(rs.getString(1));
                fieldExcelDto.setTableName(rs.getString(2));
                fieldExcelDto.setFieldNameEn(rs.getString(3));
                fieldExcelDto.setFieldType(rs.getString(4));
                fieldExcelDto.setFieldLength(rs.getString(5));
                fieldExcelDto.setFieldDescription(rs.getString(6));
                fieldExcelDto.setFieldNameZh(rs.getString(6));
                fieldExcelDto.setGenTime(today);
                fieldExcelDto.setDatalinkName(datalinkName);
                fieldExcelDto.setDatabaseName(dbNames);
                fieldExcelDto.setIsPrimaryKey(rs.getString(7));

                fieldExcelDto.setIsNullable(rs.getString(8));
                result.add(fieldExcelDto);
            }
            return result;
        } catch (SQLException e) {
            log.info(e.getMessage());
            throw new BusinessException(BusinessErrorEnum.BASE_INTERNAL_SERVER_ERROR);
        } finally {
            close(rs, ps, conn);
        }
    }

    @Override
    public List<FieldEmptyExcelDto> getAllEmptyRateFields(String datalinkName, String dbNames, String schemas,
        Map<String, List<String>> fieldTableMap) {
        log.info("todo");
        return null;
    }
}
